package com.guinea.svw.dao;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.guinea.core.utils.StringUtils;
import com.guinea.svw.entity.City;
import com.guinea.svw.entity.District;
import com.guinea.svw.entity.Province;
import com.guinea.svw.entity.SvwUser;

public class SvwJdbcDao extends JdbcDaoSupport {
	DateTimeFormatter dtf = DateTimeFormat.forPattern("yyyyMMddHHmmss");
	public List getAllUsers(){
		String sql = "";
		sql += " SELECT a.userid, b.grpcode, a.loginname, a.passwd, a.empid, d.empname, d.email, c.realgrptype,";
		sql += "        c.chnname";
		sql += "   FROM auth_usermsg a, auth_grprel b, auth_grptype c, auth_emply d";
		sql += "  WHERE a.empid = d.empid";
		sql += "    AND a.grpid = b.grpid";
		sql += "    AND b.grptype = c.grptype";
		sql += "    AND a.workflg = '0'";
		List result = this.getJdbcTemplate().query(sql,new Object[]{},new RowMapper(){
			public Object mapRow(ResultSet rs, int i) throws SQLException {
				SvwUser user = new SvwUser();
				user.setUserId(new Long(rs.getLong("USERID")));
				user.setLoginName(rs.getString("LOGINNAME"));
				user.setPassword(rs.getString("PASSWD"));
				user.setEmployeeId(new Long(rs.getLong("EMPID")));
				user.setEmployName(rs.getString("EMPNAME"));
				user.setRealGroupType(rs.getString("REALGRPTYPE"));
				user.setRealGroupName(rs.getString("CHNNAME"));
				user.setEmail(rs.getString("EMAIL"));
				return user;
			}
		});
		return result;
	}

	public List getProvinces(){
		String sql = "";
		sql += " SELECT   *";
		sql += "   FROM (SELECT vprovcode, vprovname, vprovshortname, vprovabb,";
//		try {
//			if(!getDataSource().getConnection().getMetaData().getDriverName().equalsIgnoreCase("H2 JDBC Driver"))
//				sql += "                visduchy, TO_NUMBER (vdisporder) o";
//			else
				sql += "                visduchy, vdisporder o";
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
		sql += "           FROM tdn_province";
		sql += "          WHERE vprovcode <> '00')";
		sql += " ORDER BY o";
		List result = this.getJdbcTemplate().query(sql,new Object[]{},new RowMapper(){
			public Object mapRow(ResultSet rs, int i) throws SQLException {
				Province province = new Province();
				province.setId(rs.getString("VPROVCODE"));
				province.setCode(rs.getString("VPROVCODE"));
				province.setName(rs.getString("VPROVNAME"));
				province.setShortName(rs.getString("VPROVSHORTNAME"));
				province.setOrder(new Long(rs.getLong("O")));
				province.setAbbr(rs.getString("VPROVABB"));
				province.setIsDuchy(rs.getString("VISDUCHY"));
				return province;
			}
		});
		return result;
	}
	
	public List getCities(){
		String sql = "";
		sql += " SELECT   *";
		sql += "     FROM (SELECT vcitycode, vcityname, vcityshortname, vprovcode,";
//		try {
//			if(!getDataSource().getConnection().getMetaData().getDriverName().equalsIgnoreCase("H2 JDBC Driver"))
//				sql += "                  TO_NUMBER (vdisporder) o";
//			else
				sql += "                  vdisporder o";
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
		sql += "             FROM tdn_city";
		sql += "            WHERE vcitycode <> '0' AND vprovcode <> '00')";
		sql += " ORDER BY vprovcode, o";
		List result = this.getJdbcTemplate().query(sql,new Object[]{},new RowMapper(){
			public Object mapRow(ResultSet rs, int i) throws SQLException {
				City city = new City();
				city.setId(rs.getString("VCITYCODE"));
				city.setCode(rs.getString("VCITYCODE"));
				city.setName(rs.getString("VCITYNAME"));
				city.setParentId(rs.getString("VPROVCODE"));
				city.setShortName(rs.getString("VCITYSHORTNAME"));
				city.setOrder(new Long(rs.getLong("O")));
				return city;
			}
		});
		return result;
	}
	
	public List getDistricts(){
		String sql = "";
		sql += " SELECT   *";
		sql += "     FROM (SELECT vdist_code, vdistname, vprovcode, vcitycode, vareacode,";
//		try {
//			if(!getDataSource().getConnection().getMetaData().getDriverName().equalsIgnoreCase("H2 JDBC Driver"))
//				sql += "                  vzip_code, TO_NUMBER (vdisporder) o";
//			else
				sql += "                  vzip_code, vdisporder o";
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
		sql += "             FROM tdn_district where vdist_code is not null)";
		sql += " ORDER BY vprovcode, vcitycode, o";
		List result = this.getJdbcTemplate().query(sql,new Object[]{},new RowMapper(){
			public Object mapRow(ResultSet rs, int i) throws SQLException {
				District district = new District();
				district.setId(rs.getString("VDIST_CODE"));
				district.setCode(rs.getString("VDIST_CODE"));
				district.setName(rs.getString("VDISTNAME"));
				district.setCityCode(rs.getString("VCITYCODE"));
				district.setParentId(rs.getString("VCITYCODE"));
				district.setProvinceCode(rs.getString("VPROVCODE"));
				district.setAreaCode(rs.getString("VAREACODE"));
				district.setZip(rs.getString("VZIP_CODE"));
				district.setOrder(new Long(rs.getLong("O")));
				return district;
			}
		});
		return result;
	}
	
	/**取得申请单流水号
	 * @param provinceId
	 * @return
	 */
	public long getSerialNext(String provinceId){
		long result = 0;
		if(StringUtils.isBlank(provinceId)) return result;
		String sql = " select * from skoru_serial s where s.province_id = ?";
		List l = this.getJdbcTemplate().query(sql,new Object[]{provinceId},new RowMapper(){
			public Object mapRow(ResultSet rs, int i) throws SQLException {
				return new Long(rs.getLong("SERIAL"));
			}
		});
		if(l != null && l.size()>0){
			result = ((Long)l.get(0)).longValue() + 1;
			sql = "update skoru_serial set serial = ? where province_id = ?";
			this.getJdbcTemplate().update(sql,new Object[]{new Long(result),provinceId});
		}else{
			result ++;
			sql = "insert into skoru_serial(serial,province_id) values(?, ?)";
			this.getJdbcTemplate().update(sql,new Object[]{new Long(result),provinceId});
		}
		return result;
	}
	
//	public List getApplicationById(String id){
//		String sql = "";
//		sql += " select * ";
//		sql += " from tdn_recruit_apply_info a ";
//		sql += " where a.vapply_id = ?";
//		List result = this.getJdbcTemplate().query(sql,new Object[]{id},new RowMapper(){
//			public Object mapRow(ResultSet rs, int i) throws SQLException {
//				Application application = new Application();
//				application.setId(new Long(rs.getString("VAPPLY_ID")));
//				application.setLocusProvinceId(rs.getString("VPROV_ID"));
//				application.setLocusCityId(rs.getString("VCITY_ID"));
//				application.setLocusDistrictId(rs.getString("VDIST_ID"));
//				application.setCorp(rs.getString("VFULL_NAME"));
//				application.setEconomicNature(rs.getString("VECONOMIC_NATURE"));
//				application.setIsJointStock(rs.getString("VIS_JOINT_STOCK"));
//				application.setMajorShareholder1(rs.getString("VMAJOR_SHAREHOLDERS1"));
//				application.setStake1(dts(rs.getBigDecimal("NSTAKE1")));
//				application.setMajorShareholder2(rs.getString("VMAJOR_SHAREHOLDERS2"));
//				application.setStake2(dts(rs.getBigDecimal("NSTAKE2")));
//				application.setMajorShareholder3(rs.getString("VMAJOR_SHAREHOLDERS3"));
//				application.setStake3(dts(rs.getBigDecimal("NSTAKE3")));
//				application.setMajorShareholder4(rs.getString("VMAJOR_SHAREHOLDERS4"));
//				application.setStake4(dts(rs.getBigDecimal("NSTAKE4")));
//				application.setOtherShareholders(rs.getString("VMAJOR_SHAREHOLDERS5"));
//				application.setStake5(dts(rs.getBigDecimal("NSTAKE5")));
//				application.setChairMan(rs.getString("VCHAIRMAN"));
//				application.setLastFinanceYear(rs.getString("VFINANCE_YEAR"));
//				application.setFlowedDebts(dts(rs.getBigDecimal("NFLOWED_DEBTS")));
//				application.setFlowedAsserts(dts(rs.getBigDecimal("NFLOWED_ASSETS")));
//				application.setTotalDebts(dts(rs.getBigDecimal("NTOTAL_DEBTS")));
//				application.setTotalAsserts(dts(rs.getBigDecimal("NTOTAL_ASSETS")));
//				application.setInitialStockVacancy(dts(rs.getBigDecimal("NINITIAL_STOCK_VACANCY")));
//				application.setFinalStockVacancy(dts(rs.getBigDecimal("NFINAL_STOCK_VANCANCY")));
//				application.setOperationCost(dts(rs.getBigDecimal("NOPERATION_COST")));
//				application.setMainBusinessTurnover(dts(rs.getBigDecimal("NMAIN_BUSINESS_TURNOVER")));
//				application.setNetProfit(dts(rs.getBigDecimal("NNET_PROFIT")));
//				application.setBeginSalesYear(rs.getString("VBEGAN_SALES_YEAR"));
//				application.setBeginSalesMonth(rs.getString("VBEGAN_SALES_MONTH"));
//				application.setBeginServiceYear(rs.getString("VBEGAN_SERVICE_YEAR"));
//				application.setBeginServiceMonth(rs.getString("VBEGAN_SERVICE_MONTH"));
//				application.setLastYear(rs.getString("VLICENSE_YEAR"));
//				application.setCount4s(dtl(rs.getBigDecimal("N4S_VOLUME")));
//				application.setCountSale4s(dtl(rs.getBigDecimal("N4S_RETAIL_VOLUME")));
//				application.setCountService4s(dtl(rs.getBigDecimal("N4S_SERVICE_VOLUME")));
//				application.setHasOther4s(rs.getString("VHAS_OTHER_4S"));
//				application.setHas2handLicense(rs.getString("VHAS_2ND_LICENSE"));
//				application.setIsRental(rs.getString("VIS_CAR_RENTAL"));
//				application.setContactProvinceId(rs.getString("VCONTACT_PROV_ID"));
//				application.setContactCityId(rs.getString("VCONTACT_CITY"));
//				application.setContactDistrictId(rs.getString("VCONTACT_DIST"));
//				application.setContactDetail(rs.getString("VCONTACT_DETAIL"));
//				application.setPostcode(rs.getString("VCONTACT_POSTCODE"));
//				application.setContactor(rs.getString("VCONTACTOR"));
//				application.setTel(rs.getString("VCONTACT_TEL"));
//				application.setFax(rs.getString("VCONTACT_FAX"));
//				application.setCellphone(rs.getString("VCONTACT_MOBILE"));
//				application.setEmail(rs.getString("VCONTACT_EMAIL"));
//				application.setIsSubmitted(rs.getString("VIS_SUBMITTED"));
//				application.setCreated(std(rs.getString("DCRT_DATE")));
//				application.setModified(std(rs.getString("DUPD_DATE")));
//				return application;
//			}
//		});
//		return result;
//	}
	
	public List getApplicationIds(){
		String sql = "";
		sql += " select * ";
		sql += " from tdn_recruit_apply_info a ";
		List result = this.getJdbcTemplate().query(sql,new Object[]{},new RowMapper(){
			public Object mapRow(ResultSet rs, int i) throws SQLException {
				return rs.getString("VAPPLY_ID");
			}
		});
		return result;
	}
	
//	public List getApplicationsRaw(){
//		String sql = "";
//		sql += " select * ";
//		sql += " from tdn_recruit_apply_info a ";
//		List result = this.getJdbcTemplate().query(sql,new Object[]{},new RowMapper(){
//			public Object mapRow(ResultSet rs, int i) throws SQLException {
//				Application application = new Application();
//				application.setId(new Long(rs.getString("VAPPLY_ID")));
//				application.setLocusProvinceId(rs.getString("VPROV_ID"));
//				application.setLocusCityId(rs.getString("VCITY_ID"));
//				application.setLocusDistrictId(rs.getString("VDIST_ID"));
//				application.setCorp(rs.getString("VFULL_NAME"));
//				application.setEconomicNature(rs.getString("VECONOMIC_NATURE"));
//				application.setIsJointStock(rs.getString("VIS_JOINT_STOCK"));
//				application.setMajorShareholder1(rs.getString("VMAJOR_SHAREHOLDERS1"));
//				application.setStake1(dts(rs.getBigDecimal("NSTAKE1")));
//				application.setMajorShareholder2(rs.getString("VMAJOR_SHAREHOLDERS2"));
//				application.setStake2(dts(rs.getBigDecimal("NSTAKE2")));
//				application.setMajorShareholder3(rs.getString("VMAJOR_SHAREHOLDERS3"));
//				application.setStake3(dts(rs.getBigDecimal("NSTAKE3")));
//				application.setMajorShareholder4(rs.getString("VMAJOR_SHAREHOLDERS4"));
//				application.setStake4(dts(rs.getBigDecimal("NSTAKE4")));
//				application.setOtherShareholders(rs.getString("VMAJOR_SHAREHOLDERS5"));
//				application.setStake5(dts(rs.getBigDecimal("NSTAKE5")));
//				application.setChairMan(rs.getString("VCHAIRMAN"));
//				application.setLastFinanceYear(rs.getString("VFINANCE_YEAR"));
//				application.setFlowedDebts(dts(rs.getBigDecimal("NFLOWED_DEBTS")));
//				application.setFlowedAsserts(dts(rs.getBigDecimal("NFLOWED_ASSETS")));
//				application.setTotalDebts(dts(rs.getBigDecimal("NTOTAL_DEBTS")));
//				application.setTotalAsserts(dts(rs.getBigDecimal("NTOTAL_ASSETS")));
//				application.setInitialStockVacancy(dts(rs.getBigDecimal("NINITIAL_STOCK_VACANCY")));
//				application.setFinalStockVacancy(dts(rs.getBigDecimal("NFINAL_STOCK_VANCANCY")));
//				application.setOperationCost(dts(rs.getBigDecimal("NOPERATION_COST")));
//				application.setMainBusinessTurnover(dts(rs.getBigDecimal("NMAIN_BUSINESS_TURNOVER")));
//				application.setNetProfit(dts(rs.getBigDecimal("NNET_PROFIT")));
//				application.setBeginSalesYear(rs.getString("VBEGAN_SALES_YEAR"));
//				application.setBeginSalesMonth(rs.getString("VBEGAN_SALES_MONTH"));
//				application.setBeginServiceYear(rs.getString("VBEGAN_SERVICE_YEAR"));
//				application.setBeginServiceMonth(rs.getString("VBEGAN_SERVICE_MONTH"));
//				application.setLastYear(rs.getString("VLICENSE_YEAR"));
//				application.setCount4s(dtl(rs.getBigDecimal("N4S_VOLUME")));
//				application.setCountSale4s(dtl(rs.getBigDecimal("N4S_RETAIL_VOLUME")));
//				application.setCountService4s(dtl(rs.getBigDecimal("N4S_SERVICE_VOLUME")));
//				application.setHasOther4s(rs.getString("VHAS_OTHER_4S"));
//				application.setHas2handLicense(rs.getString("VHAS_2ND_LICENSE"));
//				application.setIsRental(rs.getString("VIS_CAR_RENTAL"));
//				application.setContactProvinceId(rs.getString("VCONTACT_PROV_ID"));
//				application.setContactCityId(rs.getString("VCONTACT_CITY"));
//				application.setContactDistrictId(rs.getString("VCONTACT_DIST"));
//				application.setContactDetail(rs.getString("VCONTACT_DETAIL"));
//				application.setPostcode(rs.getString("VCONTACT_POSTCODE"));
//				application.setContactor(rs.getString("VCONTACTOR"));
//				application.setTel(rs.getString("VCONTACT_TEL"));
//				application.setFax(rs.getString("VCONTACT_FAX"));
//				application.setCellphone(rs.getString("VCONTACT_MOBILE"));
//				application.setEmail(rs.getString("VCONTACT_EMAIL"));
//				application.setIsSubmitted(rs.getString("VIS_SUBMITTED"));
//				application.setCreated(std(rs.getString("DCRT_DATE")));
//				application.setModified(std(rs.getString("DUPD_DATE")));
//				return application;
//			}
//		});
//		return result;
//	}
//	public List getOther4sByApplicationId(String applicationId){
//		String sql = "";
//		sql += " select * ";
//		sql += " from tdn_recruit_apply_other4s a ";
//		sql += " where a.vapply_id = ?";
//		List result = this.getJdbcTemplate().query(sql,new Object[]{applicationId},new RowMapper(){
//			public Object mapRow(ResultSet rs, int i) throws SQLException {
//				Other4s other4s = new Other4s();
//				other4s.setId(new Long(rs.getString("VOTHER4S_ID")));
//				other4s.setApplicationId(new Long(rs.getString("VAPPLY_ID")));
//				other4s.setBrand(rs.getString("VBRAND"));
//				other4s.setAmount(dtl(rs.getBigDecimal("V4S_AMOUNT")));
//				other4s.setAnnualSales(dtl(rs.getBigDecimal("NANNUAL_SALES")));
//				other4s.setAnnualService(dtl(rs.getBigDecimal("NANNUAL_SERVICE")));
//				other4s.setStartingTime(rs.getString("VACCESS_TIME"));
//				other4s.setCreated(std(rs.getString("DCRT_DATE")));
//				other4s.setModified(std(rs.getString("DUPD_DATE")));
//				
//				return other4s;
//			}
//		});
//		return result;
//	}
	
//	public List getRegionsByApplicationId(String applicationId){
//		String sql = "";
//		sql += " select b.vcity_info_id originid,b.visplaned,b.vprov_id,b.vcity_id,b.vdist_id,a.*";
//		sql += " from tdn_recruit_construction a left outer join tdn_recruit_city_info b on a.vcity_info_id = b.vcity_info_id";
//		sql += " where a.vapply_id = ?";
//		List result = this.getJdbcTemplate().query(sql,new Object[]{applicationId},new RowMapper(){
//			public Object mapRow(ResultSet rs, int i) throws SQLException {
//				Region region = new Region();
//				region.setId(new Long(rs.getString("VCONSTRUCTION_ID")));
//				region.setApplicationId(new Long(rs.getString("VAPPLY_ID")));
//				region.setAddress(rs.getString("VFULL_ADDRESS"));
//				region.setMethodCode(rs.getString("VCONSTRUCTION"));
//				region.setMethodOther(rs.getString("VCONSTRUCTION_OTHER"));
//				region.setPowerCode(rs.getString("VRIGHT"));
//				region.setAcceptYear(rs.getString("VCOMMIT_YEAR"));
//				region.setAcceptMonth(rs.getString("VCOMMIT_MONTH"));
//				region.setIsCollected("1".equals(rs.getString("VIS_HISTORY"))?"0":"1");
//				region.setStatus("1".equals(rs.getString("VIS_DELETED"))? "0":"1");
//				
//				if(rs.getString("ORIGINID") == null){
//					region.setTownId(new Long(0));
//					region.setIsExpected("0");
//				}else{
//					region.setTownId(new Long(rs.getString("ORIGINID")));
//					region.setIsExpected(rs.getString("VISPLANED"));
//					region.setProvinceId(rs.getString("VPROV_ID"));
//					region.setCityId(rs.getString("VCITY_ID"));
//					region.setDistrictId(rs.getString("VDIST_ID"));
//				}
//				region.setCreated(std(rs.getString("DCRT_DATE")));
//				region.setModified(std(rs.getString("DUPD_DATE")));
//				
//				return region;
//			}
//		});
//		return result;
//	}
	private String dts(BigDecimal src){
		return src == null ? null : src.toString();
	}
	private Long dtl(BigDecimal src){
		return src == null ? null : new Long(src.longValue());
	}
	private Date std(String src){
		return src == null ? null : dtf.parseDateTime(src).toDate();
	}
}
